﻿/*
 * 文件名：Performance.cs
 * 类名：XHCTMS.DAL.Performance
 * 创建人：杨学鹏
 * 创建时间：2013-5-20
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    public class Performance
    {
        /// <summary>
        /// 对平时表现的操作
        /// </summary>
        /// <param name="Performance">平时表现信息</param>
        /// <returns>返回int类型 1/成功, 0/失败, -1/异常</returns>
        public int Add(MODEL.Performance Performance)
        {
            string SqlStatement = @"insert into performance values(@performance_student_id,@performance_class_id,
            @performance_teaching_Lesson_id,@performance_score,@performance_reason,@performance_time)";

            SqlParameter[] Para = {
                                      new SqlParameter("@performance_student_id",SqlDbType.Int),
                                      new SqlParameter("@performance_class_id",SqlDbType.Int),
                                      new SqlParameter("@performance_teaching_Lesson_id",SqlDbType.Int),
                                      new SqlParameter("@performance_score",SqlDbType.Int),
                                      new SqlParameter("@performance_reason",SqlDbType.NVarChar,32),
                                      new SqlParameter("@performance_time",SqlDbType.NVarChar,32)
                                  };

            Para[0].Value = Performance.PerformanceStudentId;
            Para[1].Value=Performance.PerformanceClassId;
            Para[2].Value=Performance.PerformanceTeachingLessonId;
            Para[3].Value=Performance.PerformanceScore;
            Para[4].Value=Performance.PerformanceReason;
            Para[5].Value = Performance.PerformanceTime;

            return new DBHELP.Common().ExcuteQuery(SqlStatement, Para);
        }

        /// <summary>
        /// 按条件查询用户信息并分页
        /// </summary>
        /// <param name="PageIndex">第N页</param>
        /// <param name="PageSize">每页多少条</param>
        /// <param name="QueryString">查询条件</param>
        /// <returns>返回DataTable类型  null/异常</returns>
        public DataTable View(int PageIndex = 1, int PageSize = 50, string QueryString = "")
        {
            string SqlStatement = @"select top (@pagesize) student.student_name,class.class_name,teaching.teaching_lesson,
            performance.per_score,performance.per_reason,performance.per_time  
            from performance,class,student,teaching where performance.per_student_id=student.student_id 
            and teaching.teaching_class_Id=class.class_id and performance.per_teaching_id=
            teaching.teaching_id and performance.per_id not in
            (select top (@pagesize*(@pageindex-1)) performance.per_id  from performance,class,student,teaching where
            performance.per_student_id=student.student_id and teaching.teaching_class_Id=class.class_id
            and performance.per_teaching_id=teaching.teaching_id and (student.student_name like '%'+@Query+'%'
            or class.class_name like '%'+@Query+'%' or teaching.teaching_lesson like '%'+@Query+'%' or
            performance.per_score like '%'+@Query+'%' or performance.per_reason like '%'+@Query+'%' or 
            performance.per_time like '%'+@Query+'%'))
            and (student.student_name like '%'+@Query+'%'
            or class.class_name like '%'+@Query+'%' or teaching.teaching_lesson like '%'+@Query+'%' or
            performance.per_score like '%'+@Query+'%' or performance.per_reason like '%'+@Query+'%' or 
            performance.per_time like '%'+@Query+'%')";

            SqlParameter[] Para ={
                                     new SqlParameter("@pageindex",SqlDbType.Int),
                                     new SqlParameter("@pagesize",SqlDbType.Int),
                                     new SqlParameter("@Query",SqlDbType.VarChar,32)
                                };

            Para[0].Value = PageIndex.ToString();
            Para[1].Value = PageSize.ToString();
            Para[2].Value = QueryString;

            return new DBHELP.Common().GetDataTable(SqlStatement.ToString(), Para);
        }

        /// <summary>
        /// 取得跟条件相关信息的总条数
        /// </summary>
        /// <param name="QueryString">查询条件</param>
        /// <returns>返回string null/异常</returns>
        public string GetCount(string QueryString)
        {
            string SqlStatement = @"select COUNT(student.student_id)  
            from performance,class,student,teaching where performance.per_student_id=student.student_id 
            and teaching.teaching_class_Id=class.class_id and performance.per_teaching_id=
            teaching.teaching_id
            and (student.student_name like '%'+@Query+'%'
            or teaching.teaching_lesson like '%'+@Query+'%' or
            performance.per_score like '%'+@Query+'%' or performance.per_reason like '%'+@Query+'%' or 
            performance.per_time like '%'+@Query+'%')";

            SqlParameter[] Para = { new SqlParameter("@Query", SqlDbType.VarChar, 32) };
            Para[0].Value = QueryString;
            return new DBHELP.Common().GetDataScalar(SqlStatement, Para);
        }
    }
}
